# -*- coding:utf-8 -*-
import cherrypy,os,sqlite3
from subprocess import *
from genshi.template import TemplateLoader

class Index(object):
    def __init__(self):
        #Init topic list
        #os.system("cat initdb.sql | sqlite3 db")
        pass
    
    @cherrypy.expose
    def index(self):
        cx = sqlite3.connect("./db")
        cu = cx.cursor()
        cu.execute("select * from resume")
        result = cu.fetchall()
        cx.close()
        data = {'resume':result}
        return tl.load('index.html').generate(**data).render(encoding="UTF-8")

    @cherrypy.expose
    def doVote(self,id="",email="", links="",delete="",notes=""):
        id = self.chomp(id)
        email = self.chomp(email)
        links = self.chomp(links)
        notes = self.chomp(notes)
        cx = sqlite3.connect("./db")
        cu = cx.cursor()
        if delete:
            cu.execute("delete from resume where resumeid='%s'" %id.decode("UTF-8"))
        else:
            if "://" not in links:
                links = "http://" + links
            #print "update resume set email='%s',links='%s',notes='%s' where resumeid='%s'" %(str(email).decode("UTF-8"), str(links).decode("UTF-8"), str(notes).decode("UTF-8"),str(id).decode("UTF-8"))
            cu.execute("update resume set email='%s',links='%s',notes='%s' where resumeid='%s';" %(email.decode("UTF-8"),links.decode("UTF-8"),notes.decode("UTF-8"),id.decode("UTF-8")))
        cx.commit()
        cx.close()
        raise cherrypy.HTTPRedirect("index")

    @cherrypy.expose
    def doAdd(self, newresumeid="", newname="",newphone="",newemail="",newunivercity="",newfcompany="",newexper="", newlink="" ):
        newresumeid = self.chomp(newresumeid)
        newname = self.chomp(newname)
        newphone = self.chomp(newphone)
        newemail = self.chomp(newemail)
        newunivercity = self.chomp(newunivercity)
        newefcompany = self.chomp(newfcompany)
        newexper = self.chomp(newexper)
        newlink = self.chomp(newlink)
        print "XXXXXXX The link values is:'%s'" %(newlink.decode("UTF-8"))
        url = "scp "+ newlink + " root@147.2.207.240:/srv/www/htdocs/hr/resume"
        print "NNNNXXXX'%s'" %(url.decode("UTF-8"))
        #os.system(url)
        if "://" not in newlink:
            newlink = "http://" + newlink
        cx = sqlite3.connect("./db")
        cu = cx.cursor()
        cu.execute("select resumeid from resume")
        resume = cu.fetchall()
        tls = []
        for col in range(len(resume)):
            tls.append(resume[col][0])
        if newresumeid != "" and newname != "" and newphone != "" and newresumeid.decode("UTF-8") not in tls:
            #print "insert into topics values ('%s','%s','%s','%s','%s', '','%s')" %(newtopic.decode("UTF-8"), newdescription.decode("UTF-8"), newsponsor.decode("UTF-8"), newlink.decode("UTF-8"))
            print "insert into resume values ('%s','%s','%s','%s','%s','%s','%s','','%s')" %(newresumeid.decode("UTF-8"),newname.decode("UTF-8"),newphone.decode("UTF-8"),newemail.decode("UTF-8"),newunivercity.decode("UTF-8"),newfcompany.decode("UTF-8"),newexper.decode("UTF-8"),newlink.decode("UTF-8"))
            cu.execute("insert into resume values ('%s','%s','%s','%s','%s','%s','%s','%s','')" %(newresumeid.decode("UTF-8"),newname.decode("UTF-8"),newphone.decode("UTF-8"),newemail.decode("UTF-8"),newunivercity.decode("UTF-8"),newfcompany.decode("UTF-8"),newexper.decode("UTF-8"),newlink.decode("UTF-8")))
            cx.commit()
        else:
            return "Failed!\n\nName, email address are required, and candidate's name is uniq."
        cx.close()
        raise cherrypy.HTTPRedirect("index")

    def chomp(self, astr=""):
        if "'" in astr:
            bstr = astr.replace("'", "`")
            return bstr
        return astr

if __name__ == '__main__':
    tl = TemplateLoader(['./'])
    cherrypy.config.update('cherrypy.conf')
    cherrypy.quickstart(Index())
